using System;
using System.Data;
using System.Data.OleDb;

using Geekees.App.Cashbook.Lib.Entity;
using Geekees.App.Cashbook.Lib.Helper;

namespace Geekees.App.Cashbook.Lib.DAL
{
	/// <summary>
	/// Summary description for DACashItem.
	/// </summary>
	public class DACashItem
	{
		public DACashItem()
		{

		}

		public static DataTable GetAllCashItem( int userId )
		{
			//string sql = "SELECT * FROM [CashItem] WHERE UserId=@UserId";
			string sql = "SELECT [CashItem].*, "+
								"[Category].[Name] AS CategoryName, "+
								"[Classification].[Id] AS ClassificationId, "+
								"[Classification].[Name] AS ClassificationName "+
						"FROM ([CashItem] INNER JOIN [Category] ON ([CashItem].[CategoryId]=[Category].[Id])) "+
								"INNER JOIN [Classification] ON ([Category].[ClassificationId]=[Classification].[Id]) "+
						"WHERE [CashItem].[UserId]=@UserId "+
						"ORDER BY [CashItem].[CreateTime] DESC";
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				DataTable dt = h.ExecuteDataTable( cmd );

				return dt;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }		
		}

		public static DataTable SearchCashItem( int userId,  DateTime fromDate, DateTime toDate, int classificationId, int categoryId, CashItemType type, OrderByType order )
		{
			string ws = " WHERE [CashItem].[UserId]=@UserId ";
			if( fromDate != Consts.MIN_DATE )
				ws += " AND [CashItem].[CreateTime]>@FromDate ";
			if( toDate != Consts.MAX_DATE )
				ws += " AND [CashItem].[CreateTime]<@ToDate ";
			if( classificationId != Consts.NO_ID )
				ws += " AND [Classification].[Id]=@ClassificationId";
			if( categoryId != Consts.NO_ID )
				ws += " AND [CashItem].[CategoryId]=@CategoryId";
			if( type != CashItemType.None )
				ws += " AND [CashItem].[Type]=@Type";
			
			string orderStr = string.Empty;
			switch( order )
			{
				case OrderByType.Amount:
					orderStr = " ORDER BY [CashItem].[Amount] Desc";
					break;
				case OrderByType.CreateTime:
					orderStr = " ORDER BY [CashItem].[CreateTime] Desc";
					break;
				case OrderByType.None:
					orderStr = " ORDER BY [CashItem].[CreateTime] DESC";
					break;

			}

			string sql = "SELECT [CashItem].*, "+
				"[Category].[Name] AS CategoryName, "+
				"[Classification].[Id] AS ClassificationId, "+
				"[Classification].[Name] AS ClassificationName "+
				"FROM ([CashItem] INNER JOIN [Category] ON ([CashItem].[CategoryId]=[Category].[Id])) "+
				"INNER JOIN [Classification] ON ([Category].[ClassificationId]=[Classification].[Id]) "+
				ws +
				orderStr;
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			if( fromDate != Consts.MIN_DATE )
				cmd.Parameters.Add( "@FromDate", OleDbType.Date ).Value = fromDate;
			if( toDate != Consts.MAX_DATE )
				cmd.Parameters.Add( "@ToDate", OleDbType.Date ).Value = toDate.AddDays( 1 );
			if( classificationId != Consts.NO_ID )
				cmd.Parameters.Add( "@ClassificationId", OleDbType.Integer ).Value = classificationId;
			if( categoryId != Consts.NO_ID )
				cmd.Parameters.Add( "@CategoryId", OleDbType.Integer ).Value = categoryId;
			if( type != CashItemType.None )
				cmd.Parameters.Add( "@Type", OleDbType.Integer ).Value = (int)type;
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				DataTable dt = h.ExecuteDataTable( cmd );

				return dt;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }	
		}


		public static int AddCashItem( int userId, float amount, string detail, int categoryId, DateTime createTime, string tag , CashItemType type, DateTime actionTime )
		{
			int i = Consts.NO_ID;
			string sql = "INSERT INTO [CashItem] ( Amount, Detail, CategoryId, CreateTime, UserId, Tag, Type, ActionTime ) VALUES " + 
				         "( @Amount,  @Detail, @CategoryId, @CreateTime, @UserId, @Tag, @Type, @ActionTime )" ;
			
			string sqlId= " SELECT MAX(Id) AS ThisID FROM [CashItem]";

			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Amount", OleDbType.Decimal ).Value = amount;;
			cmd.Parameters.Add( "@Detail", OleDbType.VarChar ).Value = detail;
			cmd.Parameters.Add( "@CategoryId", OleDbType.Integer ).Value = categoryId;
			cmd.Parameters.Add( "@CreateTime", OleDbType.Date ).Value = createTime;
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			cmd.Parameters.Add( "@Tag", OleDbType.VarChar ).Value = tag;
			cmd.Parameters.Add( "@Type", OleDbType.Integer ).Value = (int)type;
			cmd.Parameters.Add( "@ActionTime", OleDbType.Date ).Value = actionTime;


			OleDbCommand cmdId = new OleDbCommand( sqlId );

			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				h.ExecuteSQL( cmd );

				i = (int)h.ExecuteScalar( cmdId );
			}
			catch( Exception ex ){ throw ex; }
			finally{ h.Close(); }
			
			return i;
		}

		public static bool DeleteCashItem( int itemId, int userId )
		{
			bool result = false;
			string sql = "DELETE FROM [CashItem] WHERE [Id]=@Id AND [UserId]=@UserId";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Id", OleDbType.Integer ).Value = itemId;
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
			
			return result;		
		}
	}
}
